package com.conver;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import com.bean.ColumnBean;
import com.bean.TableBean;
import com.util.MysqlUtil;
import com.util.OracleUtil;

/**
 * 将Oracle的表迁移到Mysql
 * @author PF0P65Z6
 *
 */
public class CreateTableToMysql {
    public static void main(String args[]) throws Exception {
    	long start = System.currentTimeMillis();
    	
    	int count = 0;
    	List<TableBean> tableList = OracleUtil.getAllTableName();
    	for(TableBean tableBean : tableList){
    		createTable(tableBean.getTableName(), tableBean.getTableComment());
    		count++;
    	}
//    	createTable("TEST","test");
    	
    	long end = System.currentTimeMillis();
    	System.out.println("oracle表结构迁移到mysql已完成，总共涉及表"+count+"张,用时"+(end-start)/1000+"秒");
    }

    /**
     * 将Oracle的表复制到MySQL中
     * @param tableName
     */
    public static void createTable(String tableName, String tableComment) {
        Connection conn = null;
        PreparedStatement ps = null;

        String createTableSql = getMysqlCreateSql(tableName, tableComment);
        System.out.println(createTableSql);
        
        try {
        	conn = MysqlUtil.getConnection();
            ps = conn.prepareStatement(createTableSql);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
        	MysqlUtil.releaseDB(ps);
        }
    }

    /**
     * 
     * 把oracle创建表语句改为mysql创建表语句
     * @param tableName
     * @param tableComment
     * @return
     */
    private static String getMysqlCreateSql(String tableName, String tableComment){
        List<ColumnBean> colList = OracleUtil.getColList(tableName);
        StringBuffer sb = new StringBuffer();
        String primaryKey = "";
        
        sb.append("CREATE TABLE ").append(tableName.toUpperCase()).append("(");
        for (int i = 0; i < colList.size(); i++) {
            ColumnBean col = colList.get(i);
          	sb.append(col.getColumnName()+" ").append(getMySQLType(tableName, col));
          	
            //设置字段是否为空
            if("N".equalsIgnoreCase(col.getNullAble())){
            	sb.append(" NOT NULL");
            }
            //设置主键
            if(null != col.getPrimaryKey() && !"".equals(col.getPrimaryKey())){
            	if(!"".equals(primaryKey)){
            		primaryKey += "," + col.getPrimaryKey();
            	}else{
            		primaryKey += col.getPrimaryKey();
            		
            		sb.append(" AUTO_INCREMENT");
            	}
            }
            //设置字段注释
            if(null != col.getComments() && !"".equals(col.getComments())){
            	sb.append(" COMMENT '"+col.getComments()+"'");
            }
            sb.append(",");
        }
        //设置主键
        if(!"".equals(primaryKey)){
        	sb.append("PRIMARY KEY ("+primaryKey+"),");
        	if(primaryKey.split(",").length>1){
        		System.out.println("WARN:表"+tableName+"，自动递增主键可能存在问题，主键为："+primaryKey);
        	}
        }
        
        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPACT COMMENT='"+tableComment+"'");

        return sb.toString();
    }
    
    /**
     * 把Oracle的Type转成Mysql的Type
     * @param c
     * @return
     */
    private static String getMySQLType(String tableName, ColumnBean c){
        if(c == null){
            return null;
        }
        
    	int dataLength = c.getDataLength();
    	int dataPrecision = c.getDataPrecision();
    	int dataScale = c.getDataScale();
    	String type = c.getColumnType();
        
        if("BLOB".equalsIgnoreCase(type) || "CLOB".equalsIgnoreCase(type) || "RAW".equalsIgnoreCase(type)){
            return "longtext";
        }else if("DATE".equalsIgnoreCase(type)){
            return "datetime";
        }else if("CHAR".equalsIgnoreCase(type)){
            return "char";
        }else if("VARCHAR2".equalsIgnoreCase(type)){
            if(c.getDataLength()>=2000){
                return "text";
            }else{
                return "varchar("+dataLength+")";
            }
        }else if("NUMBER".equalsIgnoreCase(type)){
            if(dataScale > 0 ){
                return "decimal("+dataPrecision+","+dataScale+")";
            }else{
            	if(dataPrecision == 0){
            		dataPrecision = dataLength;
            	}
            	
            	if(dataPrecision <= 3){
            		return "tinyint("+dataPrecision+")";
            	}else if(dataPrecision <= 5){
            		return "smallint("+dataPrecision+")";
            	}else if(dataPrecision <= 7){
            		return "mediumint("+dataPrecision+")";
            	}else if(dataPrecision <=10){
            		return "int("+dataPrecision+")";
            	}else{
            		return "bigint("+dataPrecision+")";
            	}
            }
        }

        System.out.println("WARN:表"+tableName+",字段"+c.getColumnName()+"，转换mysql，使用默认类型"+c.getColumnType());
        return c.getColumnType();
    }
}